#数据库操作
import pymysql
from string import Template
import pandas as pd
from sqlalchemy import create_engine
#创建数据库连接
connect = pymysql.connect(host='81.70.27.234',port=3306,user='root',password='010825lwj',db='filmInfo',connect_timeout=28800,max_allowed_packet=32)
engine = create_engine('mysql+pymysql://root:010825lwj@81.70.27.234:3306/filmInfo')
#创建游标
cursor = connect.cursor()
#将格式化后的数据存储到mysql中
def save_data(df):
    df.to_sql('type_year', engine, index=True)
#取出每年类型不为空的电影，返回dataframe
def req_type(year:int):
    req_sql = Template("""
                       select * from ${year}_detail where types is not null
                       """)
    result = pd.read_sql(req_sql.substitute(year= str(year)),engine)
    return result